import com.alibaba.fastjson.JSON
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.DateUtil
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.springframework.web.multipart.MultipartFile

def main(file) {
    Workbook wb
    Sheet sheet
    Row row
    List<Map<String, String>> list = null
    String cellData
    wb = readExcel(file)

    if (wb != null) {
        println("wb对象实例化完成")
        List<String> columns = getHeadList(wb)
        getHeadList(wb)
        //用来存放表中数据
        list = new ArrayList<>()
        //获取第一个sheet
        sheet = wb.getSheetAt(0)
        //获取最大行数
        int rownum = sheet.getPhysicalNumberOfRows()
        println("最大行数>> " + rownum)
        //获取第一行
        row = sheet.getRow(0)
        //获取最大列数
        int colnum = row.getPhysicalNumberOfCells()
        println("最大列数>> " + colnum)
        for (int i = 1; i < rownum; i ++) {
            Map<String, String> map = new LinkedHashMap<String, String>()
            row = sheet.getRow(i)
            if (row != null) {
                for (int j = 0 ;j < colnum ;j ++) {
                    cellData = (String) getCellFormatValue(row.getCell(j))
                    map.put(columns.get(j), cellData)
                }
            } else {
                break
            }
            list.add(map)
        }
    } else {
        println("wb对象为空")
    }
    return JSON.toJSONString(list)
}

List<String> getHeadList(Workbook wb) {
    Sheet sheet = wb.getSheetAt(0)
    Row row = sheet.getRow(0)
    int colnum = row.getPhysicalNumberOfCells()
    List<String> columns = new ArrayList<>()
    row = sheet.getRow(0)
    if (row != null) {
        for (int j = 0 ;j < colnum; j ++) {
            String cellData = (String) getCellFormatValue(row.getCell(j))
            columns.add(cellData)
        }
    }
    return columns
}

//读取excel
Workbook readExcel(MultipartFile file) {
    Workbook wb = null
    if (file == null) {
        return null
    }
    String extString = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."))
    InputStream is = null
    println("当前文件后缀 > " + extString)
    try {
        is = file.getInputStream()
        if (".xls" == extString) {
            println("HSSFWorkbook 实例化开始")
            return wb = new HSSFWorkbook(is)
        } else if (".xlsx" == extString) {
            println("XSSFWorkbook 实例化开始")
            return wb = new XSSFWorkbook(is)
        } else {
            return wb = null
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace()
    } catch (IOException e) {
        e.printStackTrace()
    }
    return wb
}

Object getCellFormatValue(Cell cell) {
    Object cellValue = null
    if (cell != null) {
        //判断cell类型
        int cellType = cell.getCellType()
        if (cellType == 0) {
            cellValue = String.valueOf(cell.getNumericCellValue())
        } else if (cellType == 2) {
            if (DateUtil.isCellDateFormatted(cell)) {
                //转换为日期格式YYYY-mm-dd
                cellValue = cell.getDateCellValue()
            } else {
                //数字
                cellValue = String.valueOf(cell.getNumericCellValue())
            }
        } else if (cellType == 1) {
            cellValue = cell.getRichStringCellValue().getString()
        } else {
            cellValue = ""
        }
    } else {
        cellValue = ""
    }
    return cellValue
}